﻿using Easy.Common;
using Easy.Domain.Core.Query;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Easy.Domain.Core
{
    public class SqlQueryProvider<T> : IQueryProvider<T> where T : class
    {
        #region Constructor

        private static string _tableName;
        private static PropertyInfo[] _propertyInfos;
        private readonly string _connectionString;

        static SqlQueryProvider()
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                _tableName = typeof(T).Name.ToPluralize();
            }

            if (_propertyInfos == null)
            {
                _propertyInfos = GetTableProperties();
            }
        }

        public SqlQueryProvider(IUnitOfWork unitOfWork)
        {
            CheckHelper.NotNull(unitOfWork, "unitOfWork");

            _connectionString = unitOfWork.ConnectionString;
        }

        #endregion

        #region public

        public IQueryable<T> GetPaged(int pageIndex = 0, int pageCount = 25, string where = "", string orderBy = "")
        {
            string sql = CreateSql(pageIndex, pageCount, where, orderBy);

            DataSet ds = ExecuteQuery(sql, CommandType.Text);

            return CreateEntities(ds);
        }

        public IQueryable<T> GetAll(string where = "", string orderBy = "")
        {
            string sql = CreateSql(0, Int32.MaxValue, where, orderBy);

            DataSet ds = ExecuteQuery(sql, CommandType.Text);

            return CreateEntities(ds);
        }

        public IQueryable<T> ExecuteProQuery(string procedureName, Dictionary<string, object> parameters)
        {
            CheckHelper.NotEmpty(procedureName, "procedureName");

            DataSet ds = ExecuteQuery(procedureName, parameters);

            return CreateEntities(ds);
        }

        #endregion

        #region private

        private static string CreateSql(int pageIndex, int pageCount, string where = "", string orderBy = "")
        {
            where = string.IsNullOrEmpty(where) ? "" : " AND " + where;
            orderBy = string.IsNullOrEmpty(orderBy) ? "" : " ORDER BY [" + orderBy + "]";

            return string.Format(GetSqlFormat(),
                    _tableName,
                    pageCount,
                    pageCount * pageIndex,
                    where,
                    orderBy);
        }

        private DataSet ExecuteQuery(string sql, CommandType commandType)
        {
            SqlHelper sqlHelper = new SqlHelper(_connectionString);

            return sqlHelper.ExecuteQuery(sql, commandType);
        }

        private DataSet ExecuteQuery(string procedureName, Dictionary<string, object> parameters)
        {
            SqlHelper sqlHelper = new SqlHelper(_connectionString);

            return sqlHelper.ExecuteQuery(procedureName, CommandType.StoredProcedure, parameters);
        }

        private static IQueryable<T> CreateEntities(DataSet dataSet)
        {
            CheckHelper.NotNull(dataSet, "dataSet");

            List<T> entitylst = new List<T>();

            if (dataSet.Tables.Count <= 0)
            {
                return entitylst.AsQueryable();
            }

            if (dataSet.Tables[0].Columns.Count <= 0)
            {
                return entitylst.AsQueryable();
            }

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                T newEntity = Activator.CreateInstance<T>();

                foreach (DataColumn column in dataSet.Tables[0].Columns)
                {
                    PropertyInfo pty = _propertyInfos.Where(p => p.Name == column.ColumnName).SingleOrDefault();

                    if (pty != null && pty.CanWrite)
                    {
                        object dbValue = row[column.ColumnName];

                        if (dbValue == DBNull.Value)
                        {
                            dbValue = null;
                        }

                        pty.SetValue(newEntity, dbValue);
                    }
                }

                entitylst.Add(newEntity);
            }

            return entitylst.AsQueryable();
        }

        private static string GetSqlFormat()
        {
            return " SELECT TOP({1}) * FROM ( SELECT [Extent1].*, row_number() OVER (ORDER BY [Extent1].[Id]) AS [row_number] FROM {0} AS [Extent1] WHERE 1 = 1 {3}) AS TEMP WHERE TEMP.[row_number] > {2} {4} ";
        }

        private static PropertyInfo[] GetTableProperties()
        {
            return typeof(T).GetProperties();
        }

        #endregion
    }
}
